﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

namespace U9API.Pub
{
    public enum EffentNextType
    {
        /// <summary>
        /// 对其他语句无任何影响 
        /// </summary>
        None,
        /// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果存在则继续执行，不存在回滚事务
        /// </summary>
        WhenHaveContine,
        /// <summary>
        /// 当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        WhenNoHaveContine,
        /// <summary>
        /// 当前语句影响到的行数必须大于0，否则回滚事务
        /// </summary>
        ExcuteEffectRows,
        /// <summary>
        /// 引发事件-当前语句必须为"select count(1) from .."格式，如果不存在则继续执行，存在回滚事务
        /// </summary>
        SolicitationEvent
    }
    public class CommandInfo
    {
        public object ShareObject = null;
        public object OriginalData = null;
        event EventHandler _solicitationEvent;
        public event EventHandler SolicitationEvent
        {
            add
            {
                _solicitationEvent += value;
            }
            remove
            {
                _solicitationEvent -= value;
            }
        }
        public void OnSolicitationEvent()
        {
            if (_solicitationEvent != null)
            {
                _solicitationEvent(this, new EventArgs());
            }
        }
        public string CommandText;
        public System.Data.Common.DbParameter[] Parameters;
        public EffentNextType EffentNextType = EffentNextType.None;
        public CommandInfo()
        {

        }
        public CommandInfo(string sqlText, SqlParameter[] para)
        {
            this.CommandText = sqlText;
            this.Parameters = para;
        }
        public CommandInfo(string sqlText, SqlParameter[] para, EffentNextType type)
        {
            this.CommandText = sqlText;
            this.Parameters = para;
            this.EffentNextType = type;
        }
    }
    public static class SQLKeywords
    {
        private static String _space = " ";
        public static String Space
        {
            get { return _space; }
        }
        private static String _and = " and ";
        public static String And
        {
            get { return _and; }
        }
        private static String _false = "0 ";
        /// <summary>
        /// 否（0）
        /// </summary>
        public static String Flase
        {
            get { return _false; }
        }
        private static String _true = "1 ";
        /// <summary>
        /// 是（1）
        /// </summary>
        public static String True
        {
            get { return _true; }
        }
        private static String _leftjoin = " Left Join ";
        public static String LeftJoin { get { return _leftjoin; } }

        private static String _rightjoin = " Right Join ";
        public static String RightJoin { get { return _rightjoin; } }
        private static String _leftbracket = " (";
        public static String LeftBracket { get { return _leftbracket; } }
        private static String _rightbracket = ") ";
        public static String RightBracket { get { return _rightbracket; } }

        private static String _from = " From ";
        public static String From { get { return _from; } }
        private static String _set = " Set ";
        public static String Set { get { return _set; } }
        private static String _where = " Where ";
        public static String Where { get { return _where; } }
        private static String _orderby = " Order By ";
        public static String OrderBy { get { return _orderby; } }
        private static String _groupby = " Group By ";
        public static String GroupBy { get { return _groupby; } }

        private static String _select = " Select ";
        public static String Select { get { return _select; } }
        private static String _insertto = " Insert To ";
        public static String InsertTo { get { return _insertto; } }
        private static String _delete = "Delete ";
        public static String Delete { get { return _delete; } }
        private static String _update = "Update ";
        public static String Update { get { return _update; } }
        private static String _on = " On ";
        public static String On { get { return _on; } }
        private static String _or = " Or ";
        public static String Or { get { return _or; } }
        private static String _in = " In ";
        public static String In { get { return _in; } }
        private static String _equal = "=";
        /// <summary>
        /// 等于
        /// </summary>
        public static String Equal { get { return _equal; } }
        private static String _dot = ".";
        /// <summary>
        /// 点
        /// </summary>
        public static String Dot { get { return _dot; } }
        private static String _comma = ",";
        /// <summary>
        /// 逗号
        /// </summary>
        public static String Comma { get { return _comma; } }
        private static String _line = "_";
        /// <summary>
        /// 下划线
        /// </summary>
        public static String Line { get { return _line; } }
        private static String _quote = "'";
        /// <summary>
        /// 引号
        /// </summary>
        public static String Quote { get { return _quote; } }

    }
    public static class DbHelperSQL
    {
        //public static string ConStr="";
        public static String GetInStr(String name, List<String> values)
        {
            if (values == null || values.Count==0)
            {
                return " and 1!=1";
            }
            return GetInStr(name, values.ToArray());
        }
        public static String GetInStr(String name, String[] values)
        {
            int size = values.Length;
            int maxsize = 600;
            StringBuilder builder = new StringBuilder();
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = values[i].Trim();
            }
            if (size == 0)
            {
                builder.Append(" and 1!=1");
            }
            else if (size <= maxsize)
            {
                builder.Append(SQLKeywords.And);
                builder.Append(name);
                builder.Append(SQLKeywords.In);
                builder.Append(SQLKeywords.LeftBracket);
                for (int i = 0; i < size; i++)
                {
                    if (i == size - 1)
                    {
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.RightBracket);
                    }
                    else
                    {
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.Comma);
                    }
                }
            }
            else
            {
                int n = size / maxsize;
                //builder.Append(" and (");
                builder.Append(SQLKeywords.And);
                builder.Append(SQLKeywords.LeftBracket);
                builder.Append(name);
                //builder.Append(" in( ");
                builder.Append(SQLKeywords.In);
                builder.Append(SQLKeywords.LeftBracket);
                for (int i = 0; i < maxsize; i++)
                {
                    if (i == maxsize - 1)
                    {
                        //builder.Append("'"); 
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.RightBracket);
                        //builder.Append("')");
                    }
                    else
                    {
                        //builder.Append("'");
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.Comma);
                        //builder.Append("',");
                    }
                }
                for (int i = 2; i <= n; i++)
                {
                    int r = i * maxsize;
                    int t = r - maxsize;
                    builder.Append(SQLKeywords.Or);
                    builder.Append(name);
                    //builder.Append(" in( ");
                    builder.Append(SQLKeywords.In);
                    builder.Append(SQLKeywords.LeftBracket);
                    for (int j = t; j < r; j++)
                    {
                        if (j == r - 1)
                        {
                            //builder.Append("'");
                            builder.Append(SQLKeywords.Quote);
                            builder.Append(values[j]);
                            builder.Append(SQLKeywords.Quote);
                            builder.Append(SQLKeywords.RightBracket);
                            //builder.Append("')");
                        }
                        else
                        {
                            //builder.Append("'");
                            builder.Append(SQLKeywords.Quote);
                            builder.Append(values[j]);
                            builder.Append(SQLKeywords.Quote);
                            builder.Append(SQLKeywords.Comma);
                            //builder.Append("',");
                        }
                    }
                }
                int t1 = n * maxsize;
                //builder.Append(" or ");
                builder.Append(SQLKeywords.Or);
                builder.Append(name);
                //builder.Append(" in( ");
                builder.Append(SQLKeywords.In);
                builder.Append(SQLKeywords.LeftBracket);
                for (int i = t1; i < size; i++)
                {
                    if (i == size - 1)
                    {
                        //builder.Append("'");
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.RightBracket);
                        builder.Append(SQLKeywords.RightBracket);
                        //builder.Append("'))");
                    }
                    else
                    {
                        //builder.Append("'");
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(values[i]);
                        builder.Append(SQLKeywords.Quote);
                        builder.Append(SQLKeywords.Comma);
                        //builder.Append("',");
                    }
                }
            }
            return builder.ToString();
        }
        public static int ExecuteSql(string SQLString, String connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    connection.Open();
                    using (SqlTransaction myTran = connection.BeginTransaction())
                    {
                        cmd.Transaction = myTran;
                        try
                        {
                            int rows = cmd.ExecuteNonQuery();
                            myTran.Commit();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            myTran.Rollback();
                            throw e;
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }
        }
        public static bool Exists(string strSql, String connectionString)
        {
            object obj = GetSingle(strSql, connectionString);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, String connectionString, params SqlParameter[] cmdParms)
        {
            object obj = GetSingle(strSql, connectionString, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        //public static object GetSingle(string SQLString, String connectionString, params SqlParameter[] cmdParms)
        //{
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        using (SqlCommand cmd = new SqlCommand())
        //        {
        //            try
        //            {
        //                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
        //                object obj = cmd.ExecuteScalar();
        //                cmd.Parameters.Clear();
        //                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        //                {
        //                    return null;
        //                }
        //                else
        //                {
        //                    return obj;
        //                }
        //            }
        //            catch (System.Data.SqlClient.SqlException e)
        //            {
        //                throw e;
        //            }
        //        }
        //    }
        //}

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, String connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList, String connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction tx = conn.BeginTransaction())
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand();
                        int count = 0;
                        //循环
                        foreach (CommandInfo myDE in cmdList)
                        {
                            string cmdText = myDE.CommandText;
                            try
                            {
                                SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                                PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                                {
                                    if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                                    {
                                        return 0;
                                    }

                                    object obj = cmd.ExecuteScalar();
                                    bool isHave = false;
                                    if (obj == null && obj == DBNull.Value)
                                    {
                                        isHave = false;
                                    }
                                    try
                                    {
                                        isHave = Convert.ToInt32(obj) > 0;
                                    }
                                    catch (Exception e)
                                    {
                                        throw new Exception("执行ToInt32失败:" + e.Message + "返回结果：" + obj.ToString());
                                    }

                                    if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                    {
                                        return 0;
                                    }
                                    if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                    {
                                        return 0;
                                    }
                                    continue;
                                }
                                int val;
                                try
                                {
                                    val = cmd.ExecuteNonQuery();
                                    count += val;
                                }
                                catch (Exception e)
                                {
                                    throw new Exception("执行ExecuteNonQuery失败:" + e.Message);
                                }
                                if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                                {
                                    return 0;
                                }
                                cmd.Parameters.Clear();
                            }
                            catch (Exception e)
                            {
                                throw new Exception("执行ExecuteSqlTran失败:" + e.Message + " cmd:" + cmdText);
                            }
                        }
                        tx.Commit();
                        return count;
                    }
                    catch (Exception e)
                    {
                        tx.Rollback();
                        throw e;
                    }
                    finally
                    {
                        if (conn != null)
                            conn.Close();
                    }
                }
            }
        }
        public static int ExecuteSql(string SQLString, String connectionString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlTransaction myTran = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, connection, myTran, SQLString, cmdParms);
                            int rows = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            myTran.Commit();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            myTran.Rollback();
                            throw e;
                        }
                    }
                }
            }
        }
        public static int ExecuteSqlNotTrans(string SQLString, String connectionString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, String connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw ex;
                }
                return ds;
            }
        }
        public static DataTable QueryAsDT(string SQLString, String connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataTable ds = new DataTable();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw ex;
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, String connectionString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        //public static object GetSingle(string SQLString)
        //{
        //    using (SqlConnection connection = new SqlConnection(ConStr))
        //    {
        //        using (SqlCommand cmd = new SqlCommand(SQLString))
        //        {
        //            try
        //            {
        //                object obj = cmd.ExecuteScalar();
        //                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
        //                {
        //                    return null;
        //                }
        //                else
        //                {
        //                    return obj;
        //                }
        //            }
        //            catch (System.Data.SqlClient.SqlException e)
        //            {
        //                throw e;
        //            }
        //        }
        //    }
        //}
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, String connectionString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 适用于大批量的in操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="fieldname"></param>
        /// <param name="fieldvalues"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataSet Query(String sql, String condition, String fieldname, String[] fieldvalues, String connectionString)
        {
            if (String.IsNullOrEmpty(fieldname) || fieldvalues == null)
            {
                return null;
            }
            StringBuilder temptablesql = new StringBuilder();
            temptablesql.Append(GetBatchExcuteSql(sql, condition, fieldname, fieldvalues));
            DataSet ds = Query(temptablesql.ToString(), connectionString);
            return ds;
        }

        public static DataSet Query(String sql, String condition, String fieldname1, String[] fieldvalues1, String fieldname2, String[] fieldvalues2, String connectionString)
        {
            if (String.IsNullOrEmpty(fieldname1) || fieldvalues1 == null||String.IsNullOrEmpty(fieldname2) || fieldvalues2 == null)
            {
                return null;
            }
            StringBuilder temptablesql = new StringBuilder();
            temptablesql.Append(GetBatchExcuteSql(sql, condition, fieldname1, fieldvalues1, fieldname2, fieldvalues2));
            DataSet ds = Query(temptablesql.ToString(), connectionString);
            return ds;
        }
        public static String GetBatchExcuteSql(String sql, String condition, String fieldname, String[] fieldvalues)
        {
            if (String.IsNullOrEmpty(fieldname) || fieldvalues == null)
            {
                return String.Empty;
            }
            String temptablename = "#temp_" + Common.GetNewID();
            StringBuilder temptablesql = new StringBuilder();
            temptablesql.Append(" create table ");
            temptablesql.Append(temptablename);
            temptablesql.AppendLine(" (field1 varchar(100));");
            for (int i = 0; i < fieldvalues.Length; i++)
            {
                String value = fieldvalues[i];
                if (!value.IsNullOrEmpty())
                {
                    temptablesql.Append(" insert into ");
                    temptablesql.Append(temptablename);
                    temptablesql.Append(" select '");
                    temptablesql.Append(value);
                    temptablesql.AppendLine("';");
                }
            }
            temptablesql.AppendLine(sql);
            if (String.IsNullOrEmpty(condition))
            {
                temptablesql.Append(" where 1=1 ");
            }
            else
            {
                temptablesql.Append(condition);
            }
            temptablesql.Append(" and ");
            temptablesql.Append(fieldname);
            temptablesql.Append(" in (select field1 from ");
            temptablesql.Append(temptablename);
            temptablesql.AppendLine(");");
            temptablesql.Append("drop table ");
            temptablesql.Append(temptablename);
            temptablesql.AppendLine(";");
            return temptablesql.ToString();
        }

        public static String GetBatchExcuteSql(String sql, String condition, String fieldname1, String[] fieldvalues1, String fieldname2, String[] fieldvalues2)
        {
            if (String.IsNullOrEmpty(fieldname1) || fieldvalues1 == null)
            {
                return String.Empty;
            }
            if (fieldvalues1.Length != fieldvalues2.Length)
            {
                throw new Exception("调用批量查询sql多参数数组长度要求一致！");
            }
            String temptablename = "#temp_" + Common.GetNewID();
            StringBuilder temptablesql = new StringBuilder();
            temptablesql.Append(" create table ");
            temptablesql.Append(temptablename);
            temptablesql.AppendLine(" (field1 varchar(100),field2 varchar(100));");
            for (int i = 0; i < fieldvalues1.Length; i++)
            {
                String value1 = fieldvalues1[i];
                String value2 = fieldvalues2[i];
                if (!value1.IsNullOrEmpty()||!value2.IsNullOrEmpty())
                {
                    temptablesql.Append(" insert into ");
                    temptablesql.Append(temptablename);
                    temptablesql.Append(" select '");
                    temptablesql.Append(value1);
                    temptablesql.AppendLine("','");
                    temptablesql.Append(value2);
                    temptablesql.AppendLine("';");
                }
            }
            temptablesql.Append(" select * from ( ");
            temptablesql.Append(sql);
            temptablesql.AppendLine(") A ");

            temptablesql.Append(" inner join ");
            temptablesql.Append(temptablename);
            temptablesql.Append(" B on A.");
            temptablesql.Append(fieldname1);
            temptablesql.Append("=B.field1 and A.");
            temptablesql.Append(fieldname2);
            temptablesql.Append("=B.field2 ");

            if (String.IsNullOrEmpty(condition))
            {
                temptablesql.Append(" where 1=1 ");
            }
            else
            {
                temptablesql.Append(condition);
            }
            temptablesql.Append("drop table ");
            temptablesql.Append(temptablename);
            temptablesql.AppendLine(";");
            return temptablesql.ToString();
        }

        public static String GetBatchExcuteSql(String sql, List<String> fieldnames, FieldInfoCollection fieldvalues)
        {
            return GetBatchExcuteSql(new List<String>() { sql }, fieldnames, fieldvalues);
        }
        public static String GetBatchExcuteSql(List<String> sqls, List<String> fieldnames, FieldInfoCollection fieldvalues)
        {
            StringBuilder temptablesql = new StringBuilder();
            if (fieldnames == null || fieldnames.Count == 0 || fieldvalues == null || fieldvalues.Count == 0)
            {
                for (int j = 0; j < sqls.Count; j++)
                {
                    String sql = sqls[j];
                    temptablesql.AppendLine(sql);
                    temptablesql.Append(" and  1!=1 ");
                }
            }
            else
            {
                int namecount = fieldnames.Count;
                if (fieldvalues.ChildCount != namecount)
                {
                    throw new Exception("字段名称总数与值字典子类型要求数目不一致！");
                }
                String temptablename = "#temp_" + Common.GetNewID();
                temptablesql.Append(" create table ");
                temptablesql.Append(temptablename);
                temptablesql.Append(" (");
                int i;
                for (i = 1; i < fieldnames.Count; i++)
                {
                    temptablesql.Append("field");
                    temptablesql.Append(i);
                    temptablesql.AppendLine(" varchar(100),");
                }
                temptablesql.Append("field");
                temptablesql.Append(i);
                temptablesql.AppendLine(" varchar(100));");
                //int valuecount = 0;
                for (i = 0; i < fieldvalues.Count; i++)
                {
                    FieldInfo fieldvalue = fieldvalues[i];
                    temptablesql.Append(" insert into ");
                    temptablesql.Append(temptablename);
                    temptablesql.Append(" select ");
                    foreach (string fieldname in fieldnames)
                    {
                        if (fieldvalue.ContainsKey(fieldname))
                        {
                            String value = fieldvalue[fieldname];
                            temptablesql.Append("'");
                            //if (value.IsNullOrEmpty())
                            //{
                            //    temptablesql.Append("''");
                            //}
                            //else
                            //{
                            temptablesql.Append(value);
                            //}
                            temptablesql.Append("'");
                            temptablesql.Append(",");
                        }
                        else
                        {
                            throw new Exception("值字典不包含Key值" + fieldname + "为的键值对！");
                        }
                    }
                    temptablesql.Remove(temptablesql.Length - 1, 1);
                    temptablesql.AppendLine(";");
                }
                for (int j = 0; j < sqls.Count; j++)
                {
                    String sql = sqls[j];
                    temptablesql.AppendLine(sql);
                    temptablesql.Append(" and  exists (select * from ");
                    temptablesql.Append(temptablename);
                    temptablesql.Append(" tempa where 1=1 ");
                    for (int x = 0; x < fieldnames.Count; x++)
                    {
                        String fieldname = fieldnames[x];
                        temptablesql.Append(" and ");
                        temptablesql.Append(fieldname);
                        temptablesql.Append(" = tempa.field");
                        temptablesql.Append(x + 1);
                    }
                    temptablesql.AppendLine(" );");
                }
                temptablesql.Append("drop table ");
                temptablesql.Append(temptablename);
                temptablesql.AppendLine(";");
            }
            return temptablesql.ToString();
        }
    }
}
